package com.wuseguang.springcloud.service.impl;


import com.alibaba.fastjson.JSONObject;
import com.wuseguang.springcloud.Classcardservice.ClassCardFieldService;
import com.wuseguang.springcloud.Classcardservice.ClassCardService;
import com.wuseguang.springcloud.Filter.RequestParameterWrapper;
import com.wuseguang.springcloud.dao.jdbc.StudentAchievementJdbc;
import com.wuseguang.springcloud.JdbcTemplate.WXJdbc;
import com.wuseguang.springcloud.service.StudentAchievementService;
import com.wuseguang.springcloud.util.*;
import com.wuseguang.springcloud.util.DateUtil;
import com.wuseguang.springcloud.util.WeChat.TemplateTools;
import com.wuseguang.springcloud.util.WeChat.WXUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;


@Service
public class StudentAchievementServiceImpl implements StudentAchievementService {

    public Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private StudentAchievementJdbc jdbc;

    //推送接口
    @Autowired
    private ClassCardService classCardService;

    @Autowired
    private ClassCardFieldService service;


    @Autowired
    private WXJdbc wXJdbc;


    /**
     * @描述 批量添加成绩科目
     * @创建人 zm
     * @创建时间 2020/4/16
     */
    @Transactional
    @Override
    public Result addSchoolAchievement(String schoolId, String terId, String list, String name, String teacherId, Integer type) {
        int i = jdbc.batchAddSchoolAchievement(schoolId, terId, list, name, teacherId, type);
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.ADD_FAILURE);
    }


    /**
     * @描述 删除成绩列表
     * @创建人 zm
     * @创建时间 2020/4/16
     */
    @Override
    @Transactional
    public Result delSchoolAchievement(Integer id, String teacherId) {
        String sql = "DELETE FROM school_achievement WHERE id=? AND teacher_id=?";
        int i = jdbc.updateByParameter(sql, id, teacherId);
        if (i > 0) {
            sql = "DELETE FROM school_achievement_course WHERE achievement_id=?";
            Long aLong = jdbc.updateByParameterGetId(sql, id);
            sql = "DELETE FROM student_achievement WHERE achievement_course_id=?";
            jdbc.updateByParameter(sql, aLong);
        } else {
            return new Result(CodeUtil.PUBLISHER_DELETE);
        }
        return new Result();
    }


    /**
     * @描述 组合查询成绩单列表
     * @创建人 zm
     * @创建时间 2020/4/16
     */
    @Override
    public Result querySchoolAchievement(String schoolId, String terId, String name, Integer indexNo, Integer rows, String teacherId, String admin, String sStime, String eStime) {
        String s = "";
        //不是管理员
        if (!"0".equals(admin)) {
            String classIds = jdbc.findClassIds(schoolId, teacherId);
            s += " AND s.class_id IN (" + classIds + ") ";
        }
        //是管理员
        if (!StringUtils.isEmpty(name)) {
            s += " AND s.achievement_name like '%" + name + "%'";
        }

        if (!StringUtils.isEmpty(sStime) && !StringUtils.isEmpty(eStime)) {
            s += " AND s.opertime like between  '" + sStime + "' and '" + eStime + "'";
        }


        s += " GROUP BY s.id ORDER BY s.id DESC";
        String sql = " SELECT  t.name tname,s.achievement_name,s.classname ,s.id,s.param,GROUP_CONCAT(c.course_name SEPARATOR '、') course_name,DATE_FORMAT(s.opertime,'%Y-%m-%d %H:%i') time FROM school_achievement_course c,school_achievement s  LEFT JOIN  teacher t ON (s.teacher_id=t.id)    WHERE  c.achievement_id=s.id AND s.school_id=?  " + s;
        logger.info("querySchoolAchievement,sql={}", sql);
        List<Map<String, Object>> list = jdbc.queryListMap(sql, schoolId);
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        PaginationUtil<?> paginationUtil = new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo);
        return new Result(paginationUtil);
    }

    /**
     * @描述 根据成绩管理id查询详情
     * @创建人 zm
     * @创建时间 2020/4/16
     */
    @Override
    public Result querySchoolAchievementById(Integer id) {//该成绩对应的id
        Map<String, Object> map = null;
        try {
            String sql = " SELECT  s.achievement_name,s.classname ,s.id,s.class_id,s.type FROM school_achievement s   WHERE  s.id=? ";
            map = jdbc.queryMap(sql, id);
            sql = "SELECT s.id,s.course_name,course_id,s.total_score FROM school_achievement_course s WHERE  s.achievement_id=? AND s.state=1 ORDER BY s.opertime,s.id";
            List<Map<String, Object>> maps = jdbc.queryListMap(sql, map.get("id"));
            map.put("courseList", maps);
            String sql1 = "SELECT s.student_id,s.student_name FROM    school_achievement_student s WHERE s.achievement_id=?";
            List<Map<String, Object>> maps1 = jdbc.queryListMap(sql1, id);
            String sql2 = "SELECT c.id,a.fraction,c.course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                    " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? ORDER BY c.opertime,c.id";
            if (maps1 != null && maps1.size() > 0) {
                for (int i = 0; i < maps1.size(); i++) {
                    Map<String, Object> map1 = maps1.get(i);
                    Object student_id = map1.get("student_id");
                    map1.put("list", jdbc.queryListMap(sql2, id, student_id));
                }
            }
            map.put("studentList", maps1);
        } catch (Exception e) {

        }
        return new Result(map);
    }


    /**
     * @描述 自动填充学生姓名
     * @创建人 zm
     * @创建时间 2020/4/16
     */
    @Override
    public Result updateAchievementStudent(Integer id, String classId) {//该成绩对应的id
        String sql = "DELETE FROM school_achievement_student  WHERE  achievement_id=? ";
        jdbc.updateByParameter(sql, id);
        sql = "INSERT INTO school_achievement_student(achievement_id,student_id,student_name) SELECT " + id + " achievement_id , id,student_name FROM student WHERE class_id=?";
        int j = jdbc.updateByParameter(sql, classId);
        String sql2 = "SELECT c.id,a.fraction,c.course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? ORDER BY c.opertime,c.id";
        if (j > 0) {
            String sql1 = "SELECT s.student_id,s.student_name FROM    school_achievement_student s WHERE s.achievement_id=? order by s.student_id";
            List<Map<String, Object>> maps1 = jdbc.queryListMap(sql1, id);
            if (maps1 != null && maps1.size() > 0) {
                for (int i = 0; i < maps1.size(); i++) {
                    Map<String, Object> map1 = maps1.get(i);
                    Object student_id = map1.get("student_id");
                    map1.put("list", jdbc.queryListMap(sql2, id, student_id));
                }
            }
            return new Result(maps1);
        }
        return new Result(CodeUtil.NO_STUDENT_DATA);
    }


    /**
     * @描述 根据id查询所有相关科目，且未被添加的科目
     * @创建人 zm
     * @创建时间 2020/4/16
     */
    @Override
    public Result queryCourseList(Integer id) {//该成绩对应的id
        List<Map<String, Object>> maps = null;
        try {
            String sql = " SELECT  s.achievement_name,s.classname ,s.id FROM school_achievement s   WHERE  s.id=? ";
            Map<String, Object> map = jdbc.queryMap(sql, id);
            sql = "SELECT s.id,s.course_name,course_id FROM school_achievement_course s WHERE  s.achievement_id=? AND s.state=0";
            maps = jdbc.queryListMap(sql, map.get("id"));
        } catch (Exception e) {

        }
        return new Result(maps);
    }


    /**
     * @描述 添加科目和删除科目
     * @创建人 zm
     * @创建时间 2020/4/17
     */
    @Override
    public Result updateAchievementCourse(Integer id, String total_score, Integer state) {//科目对应的id
        String sql = "UPDATE school_achievement_course SET total_score=?,state=?,opertime=now() WHERE id=?";
        int i = jdbc.updateByParameter(sql, total_score, state, id);
        if (i > 0) {
            if (state == 0) {
                sql = "DELETE  FROM student_achievement  WHERE achievement_course_id=?";
                jdbc.updateByParameter(sql, id);
            }
            return new Result();
        }
        return new Result(CodeUtil.MODIFICATION_FAILED);
    }

    /**
     * @描述 查询所有科目
     * @创建人 zm
     * @创建时间 2020/4/17
     */
    @Override
    public List<Map<String, Object>> querySubjectsAll(String school_id) {
        String sql = "SELECT  id,name  FROM course WHERE school_id=?";
        return jdbc.queryListMap(sql, school_id);
    }


    /**
     * @描述 批量添加成绩
     * @创建人 zm
     * @创建时间 2020/4/21
     */
    @Transactional
    @Override
    public Result batchAdditionAchievement(String list, Integer id) {
        int i = jdbc.batchAdditionAchievement(list, id);
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.OVER_MAXIMUM_SCORE);
    }


    /**
     * @描述 学生成绩统计
     * @创建人 zm
     * @创建时间 2020/4/17
     */
    @Override
    public Result queryScoreStatistics(Integer id) {//该成绩对应的id
        Map<String, Object> map = new HashMap<>();
        try {
            String sql2 = " SELECT  s.type,s.achievement_name,s.classname ,s.id,s.class_id,(SELECT COUNT(1) FROM school_achievement_student WHERE achievement_id=s.id) count,DATE_FORMAT(s.opertime,'%Y-%m-%d %H:%i') opertime FROM school_achievement s   WHERE  s.id=? ";
            map = jdbc.queryMap(sql2, id);
            String sql = " (SELECT  (SELECT SUM(total_score+0) FROM school_achievement_course WHERE achievement_id=t.achievement_id  ) sum,'总分' course_name, ROUND(AVG(fraction),2) avg,MAX(fraction) max,MIN(fraction) min,'0' FROM " +
                    " ( SELECT SUM(a.fraction+0) fraction,c.achievement_id FROM  school_achievement_student s  LEFT JOIN  school_achievement_course c  ON (s.achievement_id=c.achievement_id  )  " +
                    " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)  " +
                    "     WHERE  s.achievement_id=? GROUP BY a.student_id ) t )  UNION ALL (SELECT c.total_score sum,c.course_name,ROUND(AVG(a.fraction+0),2) avg,MAX(a.fraction+0) max,MIN(a.fraction+0) min,c.id FROM  school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                    "   LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)  WHERE c.state=1 AND s.achievement_id=? GROUP BY c.id ORDER BY c.opertime,c.id LIMIT 0, 10000)";
            List<Map<String, Object>> maps = jdbc.queryListMap(sql, id, id);
            map.put("statisticsList", maps);
            sql = "SELECT c.id,a.fraction,c.course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                    " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? GROUP BY c.id ORDER BY c.opertime,c.id";
            if ("0".equals(map.get("type").toString())) {
                sql = "(SELECT c.id,a.fraction,c.course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? GROUP BY c.id ORDER BY c.opertime,c.id LIMIT 0, 10000)" +
                        "  UNION ALL (SELECT  '0',SUM(a.fraction+0) fraction,'总分' course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        "    LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? ) ";
            }
            String sql1 = "SELECT t.student_id,t.student_name  FROM    school_achievement_student t WHERE t.achievement_id=?  ORDER BY (SELECT SUM(a.fraction+0) fraction FROM   student_achievement a WHERE t.student_id=a.student_id AND a.achievement_course_id IN ( SELECT id FROM school_achievement_course WHERE achievement_id=t.achievement_id) ) DESC";
            List<Map<String, Object>> maps1 = jdbc.queryListMap(sql1, id);
            if (maps1 != null && maps1.size() > 0) {
                for (int i = 0; i < maps1.size(); i++) {
                    Map<String, Object> map1 = maps1.get(i);
                    Object student_id = map1.get("student_id");
                    if ("0".equals(map.get("type").toString())) {
                        map1.put("list", jdbc.queryListMap(sql, id, student_id, id, student_id));
                    } else {
                        map1.put("list", jdbc.queryListMap(sql, id, student_id));
                    }
                }
            }
            map.put("courseList", jdbc.queryListMap("SELECT id,course_name FROM school_achievement_course WHERE state=1 AND achievement_id=? ORDER BY opertime,id", id));
            map.put("detailsList", maps1);
        } catch (Exception e) {
            logger.info("queryScoreStatistics,erorr={}", e);
        }
        return new Result(map);
    }


    /**
     * @描述 成绩发送给家长
     * @创建人 zm
     * @创建时间 2020/4/21
     */
    @Override
    public Result sendParent(Integer id) {//该成绩对应的id
        //小程序推送
        //achievementPush(id);
        //公众号推送
        officialAccountPush(id);
        String sql = "UPDATE school_achievement SET state=1,sendtime=now() WHERE id=?";
        int i = jdbc.updateByParameter(sql, id);
        if (i > 0) {
            sql = "select class_id ints from  school_achievement  WHERE id=?";
            Integer integer = jdbc.queryInteger(sql, id);
            service.classesAndCards(integer, "noticeflag", "1");
            classCardService.classesAndCards(integer);
        }
        return new Result();
    }


    /**
     * @描述 导出成绩模板
     * @创建人 zm
     * @创建时间 2020/3/15
     */
    @Override
    public void exportAchievement(RequestParameterWrapper request, HttpServletResponse response, Integer id, Integer type) {//成绩id
        try {
            if (type == 0) {
                String sql = "SELECT COUNT(1)  ints FROM school_achievement_student WHERE achievement_id=?";
                if (jdbc.queryInteger(sql, id) <= 0) {
                    sql = "INSERT INTO school_achievement_student(achievement_id,student_id,student_name) SELECT " + id + " achievement_id , id,student_name FROM student WHERE class_id IN (SELECT class_id FROM school_achievement WHERE id=? )";
                    jdbc.updateByParameter(sql, id);
                }
            }

            String sql = "SELECT type string FROM school_achievement WHERE id=?";
            String s = jdbc.queryString(sql, id);
            sql = "SELECT s.course_name FROM school_achievement_course s WHERE  s.achievement_id=? AND s.state=1 ORDER BY s.opertime,s.id";
            List<String> strings = jdbc.queryListString(sql, new Object[]{id});
            String sql1 = "SELECT s.student_id,s.student_name FROM    school_achievement_student s WHERE s.achievement_id=? order by s.student_id";
            String sql2 = "SELECT a.fraction FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                    " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? GROUP BY c.id ORDER BY c.opertime,c.id";

            if ("0".equals(s) && type == 1) {
                sql1 = "SELECT t.student_id,t.student_name  FROM    school_achievement_student t WHERE t.achievement_id=?  ORDER BY (SELECT SUM(a.fraction+0) fraction FROM   student_achievement a WHERE t.student_id=a.student_id AND a.achievement_course_id IN ( SELECT id FROM school_achievement_course WHERE achievement_id=t.achievement_id) ) DESC";
                sql2 = "(SELECT a.fraction FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? GROUP BY c.id ORDER BY c.opertime,c.id LIMIT 0, 10000 )" +
                        " UNION ALL (SELECT  SUM(a.fraction+0) fraction FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        "    LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? ) ";
            }
            List<Map<String, Object>> maps1 = jdbc.queryListMap(sql1, id);
            if (maps1 != null && maps1.size() > 0) {
                for (int i = 0; i < maps1.size(); i++) {
                    Map<String, Object> map1 = maps1.get(i);
                    Object student_id = map1.get("student_id");
                    if (type == 1 && "0".equals(s)) {
                        map1.put("scoreList", jdbc.queryListString(sql2, new Object[]{id, student_id, id, student_id}));
                    } else {
                        map1.put("scoreList", jdbc.queryListString(sql2, new Object[]{id, student_id}));
                    }
                }
            }
            if (type == 1 && strings != null && strings.size() > 0) {
                strings.add("总分");
            }
            downloadExcelMapTemplate(response, maps1, strings, "成绩模板");
        } catch (IOException e) {
            logger.info("导出学生成绩模板,error={}", e);
        }

    }


    public void downloadExcelMapTemplate(HttpServletResponse response, List<Map<String, Object>> list, List<String> key, String fileName) throws IOException {


        //创建ByteArrayOutputStream
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        //创建工作簿
        createWorkBookTimetable(list, key).write(os);
        //下载execl
        ExcelDownloadUtils.downloadExcel(response, os, fileName);
    }

    public Workbook createWorkBookTimetable(List<Map<String, Object>> list, List<String> key) {

        // 创建excel工作簿
        Workbook worb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = worb.createSheet("sheet1");
        // 手设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度。
        for (int i = 0; i < key.size(); i++) {
            sheet.setColumnWidth((short) i, (short) (35 * 150));
        }

        // 创建单元格格式
        CellStyle cs = worb.createCellStyle();
        // 创建字体
        Font f = worb.createFont();

        // 创建字体样式
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setFontName("宋体");

        // 设置单元格的样式
        cs.setFont(f);


        // 创建第一行
        Row row = sheet.createRow((short) 0);
        row.setHeightInPoints(20);

        Cell cell = null;


        // 创建单元格格式
        CellStyle cs2 = worb.createCellStyle();
        // 创建字体
        Font f2 = worb.createFont();

        // 创建字体样式
        f2.setFontHeightInPoints((short) 12);
        f2.setColor(IndexedColors.RED.getIndex());
        f2.setFontName("宋体");

        // 设置单元格的样式
        cs2.setFont(f2);


        // 创建第一行
        Row row2 = sheet.createRow((short) 1);
        row2.setHeightInPoints(20);
        cell = row.createCell(0);
        cell.setCellValue("学生姓名");
        cell.setCellStyle(cs2);
        // 设置列名
        for (int i = 0; i < key.size(); i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue(key.get(i));
            cell.setCellStyle(cs2);
        }
        // 设置每行每列的值
        if (!list.isEmpty()) {
            logger.info("list={}", list.toString());
            for (short i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow((short) (i + 1));
                row1.setHeightInPoints(20);
                cell = row1.createCell(0);
                //把值放进去
                cell.setCellValue(map.get("student_name").toString());
                //设置样式
                cell.setCellStyle(cs);
                // 在row行上创建一个方格
                Object scoreList = map.get("scoreList");
                if (scoreList != null) {
                    logger.info("scoreList={}", scoreList.toString());
                    List<String> scoreList1 = (List<String>) scoreList;
                    for (short j = 0; j < scoreList1.size(); j++) {
                        // 判断是否为时间格式，如果为时间格式，要进行格式转换
                        String valueS = "";
                        cell = row1.createCell(j + 1);
                        Object bufferValue = scoreList1.get(j);
                        if (bufferValue instanceof Date) {
                            //获取value
                            valueS = DateUtil.parseData((Date) bufferValue, "yyyy-MM-dd HH:mm:ss");
                        } else {
                            //获取value
                            valueS = StringUtils.isEmpty(bufferValue) ? "" : bufferValue.toString();
                        }
                        //把值放进去
                        cell.setCellValue(valueS);
                        //设置样式
                        cell.setCellStyle(cs);
                    }
                }
            }
        }
        return worb;
    }

    /**
     * 把成绩推送到小程序
     *
     * @param id 成绩id
     * @return
     */
    public int achievementPush(Integer id) {

        //查询这个成绩的所有学生
        String sql = "select sa.achievement_name,sas.student_id,sas.student_name ,sa.classname " +
                " from school_achievement sa left join school_achievement_student sas" +
                " on(sa.id=sas.achievement_id) where sa.id = ?  and sa.state = 0";

        //查询这个考试的学生数据
        List<Map<String, Object>> list = jdbc.queryListMap(sql, id);
        //循环学生
        for (Map<String, Object> map : list) {
            //查询家长的小程序openId
            sql = "select p.open_id_one,p.id,p.open_id_two  from parent p  LEFT JOIN parent_student ps on(ps.parent_id=p.id)where ps.student_id=?";
            //家长openid
            Map<String, Object> parent = jdbc.queryMap(sql, map.get("student_id"));
            //家长的openId
            String openId = (String) parent.get("open_id_one");

            sql = "select COUNT(1) count from user_subscription_template ust left JOIN we_chat_template wt " +
                    "on(wt.id=ust.template_id) where wt.template_id='fPhNmlh3qrB6C2B__YDvC7it5zcfuoF_fk3gRj3LEOI' and ust.open_id =? and ust.state=0 ";
            //判断这个用户这个小程序是否订阅了
            int count = jdbc.queryCount(sql, openId);
            //判断openId是否为空
            if (!StringUtils.isEmpty(openId) && count > 0) {

                String accessToken = wXJdbc.getAccessToken(1);

                Map<String, Object> pushMap = new HashMap<>();
                pushMap.put("name1", map.get("student_name"));
                pushMap.put("thing6", "您有一张" + map.get("achievement_name") + "的考试成绩单");

                //解析后的数据
                JSONObject json = TemplateTools.initializationData(pushMap);
                //推送订阅消息
                WXUtil.sendWechatmsgToUser(accessToken, openId, "fPhNmlh3qrB6C2B__YDvC7it5zcfuoF_fk3gRj3LEOI", "", json, "", "");
                wXJdbc.updateSubscribe(openId, "fPhNmlh3qrB6C2B__YDvC7it5zcfuoF_fk3gRj3LEOI");
            }

        }


        return 1;
    }

    /**
     * 公众号推送
     *
     * @param id
     */
    public void officialAccountPush(Integer id) {

        //获取token
        String accessToken = wXJdbc.getOfficialAccountAccessToken(0);

        //查询这个成绩的所有学生
        String sql = "select sa.achievement_name,sas.student_id,sas.student_name ,sa.classname,sa.opertime" +
                " from school_achievement sa left join school_achievement_student sas" +
                " on(sa.id=sas.achievement_id) where sa.id = ?  and sa.state = 0";

        //查询这个考试的学生数据
        List<Map<String, Object>> list = jdbc.queryListMap(sql, id);
        //查询家长的小程序openId
        sql = "select p.open_id_one,p.id,p.open_id_two  from parent p  LEFT JOIN parent_student ps on(ps.parent_id=p.id)where ps.student_id=?";
        //循环学生
        for (Map<String, Object> map : list) {

            //家长的openId
            String openId = null;
            try {
                //家长openid
                Map<String, Object> parent = jdbc.queryMap(sql, map.get("student_id"));
                openId = (String) parent.get("open_id_two");
            } catch (Exception e) {
                logger.info("成绩单此学生已不存在");
            }

            //判断openId是否为空
            if (!StringUtils.isEmpty(openId)) {
                Map<String, Object> pushMap = new HashMap<>();

                pushMap.put("first", "家长您好，您的孩子有一条新的成绩信息。");
                pushMap.put("keyword1", map.get("student_name"));
                pushMap.put("keyword2", DateUtil.parseData(DateUtil.strToDateLong(map.get("opertime").toString(),
                        "yyyy-MM-dd HH:mm:ss"), "yyyy-MM-dd HH:mm:ss"));
                pushMap.put("keyword3", DateUtil.parseData(new Date(), "yyyy-MM-dd HH:mm:ss"));
                pushMap.put("remark", "感谢您的支持。");

                //跳转小程序信息
                JSONObject appletJson = new JSONObject();
                appletJson.put("appid", "wx30b606bac7775eba");
                appletJson.put("pagepath", "pages/achievement/index");

                //解析后的数据
                JSONObject json = TemplateTools.initializationData(pushMap);
                //推送公众号信息
                WXUtil.officialAccountPush(accessToken, openId, "tz5yzdZixOgwunEsR32Knke2QRkQ4vvs3VRXQlWbWZY", "", appletJson, json);
            }
        }
    }
}